﻿DELETE FROM CARD_PERFORMANCE
delete from POS_PERFORMANCE

DECLARE @MONTH1 DATE='20151130'
DECLARE @COUNT INT =1
DECLARE @FULLDATE NVARCHAR(8)
WHILE @COUNT <13
BEGIN
	SET @FULLDATE= CONVERT (NVARCHAR(8), @MONTH1 ,112)
	IF(@MONTH1 = '20160430') SET @FULLDATE = '20160429'
	DECLARE @STRCOUNT NVARCHAR(6) = CONVERT(NVARCHAR(6),@MONTH1,112)
	
PRINT @STRCOUNT
-----------H.1.THẺ 
PRINT 'CARD PERFORMANCE'
--	EXEC('
--INSERT CARD_PERFORMANCE
--SELECT YEARMONTH, T24_DAO AS DAO, LOAI_THE, THE_KICH_HOAT,  COUNT(ACNT_CONTRACT_ID) AS NO_OF_CARD, SUM(KPI_CARD) AS KPI_CARD
--FROM (
--SELECT YEARMONTH, T24_DAO, ACNT_CONTRACT_ID,LOAI_THE ,THE_KICH_HOAT, CASE WHEN LOAI_THE=''DEBIT'' AND RN<3 THEN 1 
--WHEN LOAI_THE=''CREDIT'' AND RN< 4 THEN 5 ELSE 0 END AS KPI_CARD
--FROM 
--(
--SELECT YEARMONTH, T24_DAO, ACNT_CONTRACT_OID, ACNT_CONTRACT_ID, CASE WHEN PRODUCT_GROUP LIKE ''%DEBIT%'' THEN ''DEBIT'' ELSE ''CREDIT'' END AS LOAI_THE, 
--CASE WHEN CONVERT(NVARCHAR(6),UNLOCK_DATE,112)='+@STRCOUNT+' THEN ''NEW_ACTIVATE''
--ELSE ''ACTIVATED'' END AS THE_KICH_HOAT, ROW_NUMBER() OVER (PARTITION BY ACNT_CONTRACT_OID ORDER BY UNLOCK_DATE ASC) AS RN
--FROM HUONGTT19.CARD.DBO.CARD_ALL_'+@STRCOUNT+'
--WHERE T24_DAO IS NOT NULL
----AND UNLOCK_DATE >=''20160101''
--AND UNLOCK_CARD=''CARD_ACTIVE'' ) X) Y
--GROUP BY YEARMONTH, T24_DAO, LOAI_THE, THE_KICH_HOAT
--')
DECLARE @STRCOUNT_POS NVARCHAR(8) = @STRCOUNT
IF(@FULLDATE = '20160131') SET @STRCOUNT_POS = '20160131'
IF(@FULLDATE = '20160229') SET @STRCOUNT_POS = '20160229'
IF(@FULLDATE = '20160429') SET @STRCOUNT_POS = '20160430'
IF(@FULLDATE = '20160930') SET @STRCOUNT_POS = '20160930'
IF(@FULLDATE = '20161031') SET @STRCOUNT_POS = '20161031'

-----------------H.2.POS
PRINT 'POS PERFORMANCE'
if(@MONTH1 < '20160101')
begin
	EXEC('
Insert POS_PERFORMANCE
SELECT YEARMONTH, DAO, MAY_LAP, COUNT(DISTINCT [ID] ) AS NO_OF_POS 
FROM (
SELECT YEARMONTH='''+@STRCOUNT+''', B.DAO AS DAO, A.[ID], 
CASE WHEN CONVERT(NVARCHAR(6), [POS_OPERATION_DATE],112)='''+@STRCOUNT+''' THEN ''NEW'' ELSE ''EXISTED'' END AS MAY_LAP
FROM HUONGTT19.POS.DBO.POS_ALL_'+@STRCOUNT_POS+' A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF=B.CIF COLLATE DATABASE_DEFAULT
WHERE 
[STATUS] = ''2'' AND
--[NGÀY LẮP MÁY] >=''20160101'' AND 
b.DAO IS NOT NULL) X
GROUP BY YEARMONTH, DAO, MAY_LAP
')
end
if(@MONTH1 >= '20160101')
begin
	EXEC('
Insert POS_PERFORMANCE
SELECT YEARMONTH, DAO, MAY_LAP, COUNT(DISTINCT [MÃ] ) AS NO_OF_POS 
FROM (
SELECT YEARMONTH='''+@STRCOUNT+''', B.DAO AS DAO, A.[MÃ], 
CASE WHEN CONVERT(NVARCHAR(6), [NGÀY LẮP MÁY],112)='''+@STRCOUNT+''' THEN ''NEW'' ELSE ''EXISTED'' END AS MAY_LAP
FROM HUONGTT19.POS.DBO.POS_ALL_'+@STRCOUNT_POS+' A
LEFT JOIN TBL_CUSTOMER B
ON A.CIF=B.CIF COLLATE DATABASE_DEFAULT
WHERE 
[TRẠNG THÁI TRIỂN KHAI] = ''2'' AND
--[NGÀY LẮP MÁY] >=''20160101'' AND 
b.DAO IS NOT NULL) X
GROUP BY YEARMONTH, DAO, MAY_LAP
')
end
	SET @MONTH1 = DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@MONTH1)+2,0))
SET @COUNT = @COUNT + 1
END
--SELECT top 1 * FROM HUONGTT19.POS.[dbo].[POS_ALL_201512]

--select top 1 [MÃ],* from huongtt19.pos.[dbo].[POS_ALL_20161130]
--SELECT * FROM CARD_PERFORMANCE ORDER BY YEARMONTH,DAO, LOAI_THE

--SELECT * FROM POS_PERFORMANCE 
----------------H.3.PAYROLL
DELETE FROM FIRST_TRANS_PAYROLL

INSERT FIRST_TRANS_PAYROLL
SELECT * 
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY DEBIT_CIF ORDER BY TRANS_DATE ASC) AS RN
 FROM HUONGTT19.PAYROLL.DBO.[PAYROLL_ALL_LUY_KE])X
 WHERE X.RN=1 and YEARmonth >= '201511'
 ORDER BY YEARMONTH

 --SELECT * FROM FIRST_TRANS_PAYROLL

 DELETE FROM PAYROLL_NEW

 INSERT PAYROLL_NEW
 SELECT A.YEARMONTH, B.DAO AS DAO, COUNT(DISTINCT DEBIT_CIF) AS NO_NEW_PAYROLL
 FROM FIRST_TRANS_PAYROLL A
 LEFT JOIN TBL_CUSTOMER B
 ON A.DEBIT_CIF=B.CIF
 WHERE A.SEGMENT='SMES'
 GROUP BY A.YEARMONTH, B.DAO
 ORDER BY YEARMONTH

 --SELECT * FROM PAYROLL_NEW ORDER BY YEArmonth

 ---------------------
 --DROP TABLE PAYROLL_PERFORMANCE_LUY_KE

 delete from PAYROLL_PERFORMANCE_LUY_KE

INSERT PAYROLL_PERFORMANCE_LUY_KE
SELECT A.YEARMONTH, B.DAO AS DAO, COUNT(TRANS_CODE) AS NO_OF_TRANS, COUNT(DISTINCT DEBIT_CIF) AS NO_CIF, SUM(TRANS_AMOUNT) AS TRANS_AMOUNT, 0
FROM HUONGTT19.PAYROLL.[DBO].[PAYROLL_ALL_LUY_KE] A
LEFT JOIN TBL_CUSTOMER B
ON A.DEBIT_CIF=B.CIF
WHERE A.SEGMENT='SMES' and a.yearmonth >='201511'
GROUP BY A.YEARMONTH, B.DAO
ORDER BY YEARMONTH


--ALTER TABLE PAYROLL_PERFORMANCE_LUY_KE
--ADD NO_NEW_PAYROLL FLOAT 

UPDATE A
SET A.NO_NEW_PAYROLL=B.NO_NEW_PAYROLL
FROM PAYROLL_PERFORMANCE_LUY_KE A, PAYROLL_NEW B
WHERE A.DAO=B.DAO
AND A.YEARMONTH=B.YEARMONTH


--SELECT * FROM PAYROLL_PERFORMANCE_LUY_KE ORDER BY yearmonth
-----------------H.4.B2B 

--SELECT * FROM B2B.[DBO].[B2B_USER_20161208]

--SELECT CONVERT(NVARCHAR(6), OPEN_DATE,112) AS YEARMONTH, DAO_CIF AS DAO, COUNT(DISTINCT CIF) AS NO_B2B_USER
-- FROM HUONGTT19.B2B.[DBO].[B2B_USER_20161208]
-- WHERE SEGMENT='SMES'
-- AND OPEN_DATE <='20161130'
-- GROUP BY CONVERT(NVARCHAR(6), OPEN_DATE,112) , DAO_CIF
-- ORDER BY CONVERT(NVARCHAR(6), OPEN_DATE,112)

delete from B2B_PERFROMANCE

insert B2B_PERFROMANCE
 SELECT A.YEARMONTH, B.DAO, COUNT(TRANS_CODE) AS NO_OF_TRANS, COUNT(DISTINCT CIF) AS COUNT_CIF, SUM(AMOUT_QD) AS TRANS_AMOUNT, 0
  FROM HUONGTT19.B2B.[DBO].[B2B_TRANSFER_LUY_KE] A
LEFT JOIN TBL_CUSTOMER B
ON A.CUST_CIF=B.CIF
WHERE DAO IS NOT NULL and a.yearmonth >='201511'
GROUP BY A.YEARMONTH, B.DAO
ORDER BY YEARMONTH


--select *  
--  FROM HUONGTT19.B2B.[DBO].[B2B_TRANSFER_LUY_KE]

--SELECT * FROM B2B_PERFROMANCE
--WHERE YEARMONTH>'201501' AND NO_NEW_USER>COUNT_CIF
--ORDER BY YEARMONTH 


--ALTER TABLE B2B_PERFROMANCE
--ADD NO_NEW_USER FLOAT 

--SELECT * FROM HUONGTT19.B2B.[DBO].[B2B_USER_20161208] order by cif

--UPDATE A
--SET A.COUNT_CIF=B.NO_B2B_USER
--FROM B2B_PERFROMANCE A, 
--(SELECT CONVERT(NVARCHAR(6), CUS_OPEN_DATE,112) AS YEARMONTH, DAO_CIF AS DAO, COUNT(DISTINCT CIF) AS NO_B2B_USER
-- FROM TBL_CM_I2B
-- GROUP BY  DAO
--) B
--WHERE A.DAO=B.DAO
--AND A.YEARMONTH=B.YEARMONTH

UPDATE A
SET A.NO_NEW_USER=B.NO_B2B_USER
FROM B2B_PERFROMANCE A, 
(SELECT CONVERT(NVARCHAR(6), OPEN_DATE,112) AS YEARMONTH, DAO_CIF AS DAO, COUNT(DISTINCT CIF) AS NO_B2B_USER
 FROM HUONGTT19.B2B.[DBO].[B2B_USER_20161208]
 WHERE SEGMENT='SMES'
 AND OPEN_DATE <='20161130'
 GROUP BY CONVERT(NVARCHAR(6), OPEN_DATE,112) , DAO_CIF
) B
WHERE A.DAO=B.DAO
AND A.YEARMONTH=B.YEARMONTH

--------------------------H.5.THUẾ ĐIỆN TỬ 

--SELECT TOP 100*  FROM SERVER16.VPB_WHR2.[DBO].[TBL_TCS_CUSTOMER]

--SELECT TOP 100* FROM SERVER16.VPB_WHR2.[DBO].[TBL_TCS_CTU_HDR_TDT]